﻿using aier.dataaccess.model;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace aier.dataaccess.dapper
{
    public class ProvinceDal
    {
        public DapperHelper dapperHelper { get; set; }

        /// <summary>
        /// 根据名称查省 名称为空查全部 queryProvinceByName
        /// </summary>
        /// <param name="provincename">省份名称</param>
        /// <returns></returns>
        public List<ProvinceView> QueryProvinceByName(string new_name)
        {
            List<ProvinceView> provinceViews = null;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select * from new_province /**where**/";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (!string.IsNullOrWhiteSpace(new_name))
                {
                    sqlBuilder.Where("new_name=@new_name");
                    sqlParams.Add("@new_name", new_name);
                }
                provinceViews = a.Query<ProvinceView>(sqlTemplate.RawSql, sqlParams).ToList();
            });

            return provinceViews;
        }

        /// <summary>
        /// 根据名称查省ID
        /// </summary>
        /// <param name="provincename">省份名称</param>
        /// <returns></returns>
        public Guid GetProvinceIdByName(string new_name)
        {
            Guid ProvinceId = Guid.Empty;
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select new_provinceId from new_provincebase /**where**/";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (!string.IsNullOrWhiteSpace(new_name))
                {
                    sqlBuilder.Where("new_name=@new_name");
                    sqlParams.Add("@new_name", new_name);
                }
                ProvinceId = a.ExecuteScalar<Guid>(sqlTemplate.RawSql, sqlParams);
            });

            return ProvinceId;
        }

        /// <summary>
        /// 根据名称查省ID
        /// </summary>
        /// <param name="provinceId">省份名称</param>
        /// <returns></returns>
        public string GetProvinceNameById(string provinceId)
        {
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            return dapperHelper.ExecueWithoutTransaction(a =>
            {
                string sql = @"select new_name from new_provincebase /**where**/";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                if (!string.IsNullOrWhiteSpace(provinceId))
                {
                    sqlBuilder.Where("new_provinceId=@new_provinceId");
                    sqlParams.Add("@new_provinceId", provinceId);
                }
                return a.ExecuteScalar<string>(sqlTemplate.RawSql, sqlParams);
            });
        }
    }
}
